﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApp4.DLL
{   
    /// <summary>
    /// 所有的数据库连接方法中间件
    /// </summary>
  public   class DbUse
    {

        DbHelperByParameter parameter = new DbHelperByParameter();
        /// <summary>
        /// 登录
        /// </summary>
        /// <param name="username"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public bool OnLogin(string username,string pwd) 
        {
            var res=  parameter.GetLoginCouent(username, pwd);
            return int.Parse(res.ToString()) > 0;
     
        }

        /// <summary>
        ///  获取用户数量
        /// </summary>
        /// <param name="username"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public object GetUserCount(string username, string pwd) 
        {
            string strFull = string.Format("select count(*) from Users where UserName='{0}' and PassWord='{1}'", username, pwd);
            return parameter.GetDataCount(strFull);
        }

        /// <summary>
        /// 注册
        /// </summary>
        /// <param name="username"></param>
        /// <param name="pwd"></param>
        /// <returns></returns>
        public int OnRegister(string username, string pwd)
        {
            StringBuilder str = new StringBuilder();

            string strSql = str.Append(
           string.Format("insert into Users (UserName,PassWord) values(@username,@password)"))
          .ToString();

            //定义参数
            SqlParameter[] para = {
                 new SqlParameter("@username",SqlDbType.VarChar,20),
                 new SqlParameter("@password",SqlDbType.VarChar,20)
            };

            //给参数赋值
            para[0].Value = username.Trim();
            para[1].Value = pwd.Trim();


            return parameter.AddOrDeleteOrUpdate(strSql.ToString(), para);
        }

        /// <summary>
        /// 查询所有文章
        /// </summary>
        /// <returns></returns>
        public DataSet GetArticles() 
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from Articles");

            return parameter.ExecuteTable(strSql.ToString());
        }

        /// <summary>
        ///  模糊查询
        /// </summary>
        /// <param name="fields">列</param>
        /// <param name="viewName">表</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public DataSet GetArticlesByTitleOrAuthor(string[] fields, string viewName, Dictionary<string, string> strWhere) 
        {
            return parameter.GetView(2,fields, viewName, strWhere);
        }

        /// <summary>
        /// 新增文章
        /// </summary>
        /// <param name="title"></param>
        /// <param name="author"></param>
        /// <param name="createtime"></param>
        /// <returns></returns>
        public int AddArticle(string title,string author,string createtime) 
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("insert into Articles (Title,Author,CreatedTime) values (@title,@author,@createtime)");

            SqlParameter[] para =
            {
               new SqlParameter("@title",SqlDbType.VarChar,80),
               new SqlParameter("@author",SqlDbType.VarChar,80),
               new SqlParameter("@createtime",SqlDbType.VarChar,80),
            };

            para[0].Value = title;
            para[1].Value = author;
            para[2].Value = createtime;

            return parameter.AddOrDeleteOrUpdate(sqlStr.ToString(), para);
      
        }


        /// <summary>
        /// 更新文章
        /// </summary>
        /// <param name="title"></param>
        /// <param name="author"></param>
        /// <param name="createtime"></param>
        /// <returns></returns>
        public int UpdateArticle(int id,string title, string author, string createtime)
        {
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("update Articles set Title=@title,Author=@author,CreatedTime=@createtime where Id=@id");

            ///定义参数
            SqlParameter[] para =
            {          
               new SqlParameter("@title",SqlDbType.VarChar,80),
               new SqlParameter("@author",SqlDbType.VarChar,80),
               new SqlParameter("@createtime",SqlDbType.VarChar,80),
               new SqlParameter("@id",SqlDbType.Int,80),
            };

            para[0].Value = title;
            para[1].Value = author;
            para[2].Value = createtime;
            para[3].Value = id;

            return parameter.AddOrDeleteOrUpdate(sqlStr.ToString(), para);

        }


        /// <summary>
        /// 删除文章
        /// </summary>
        /// <param name="title"></param>
        /// <param name="author"></param>
        /// <param name="createtime"></param>
        /// <returns></returns>
        public int DeleteArticle(int id)
        {   
            StringBuilder sqlStr = new StringBuilder();
            sqlStr.Append("delete from Articles where Id=@id");

            ///定义参数
            SqlParameter[] para =
            {
            new SqlParameter("@id", SqlDbType.Int, 80),
            };
            para[0].Value = id;
             
            return parameter.AddOrDeleteOrUpdate(sqlStr.ToString(), para);

        }

        /// <summary>
        /// 获取用户
        /// </summary>
        /// <param name="Username"></param>
        /// <returns></returns>
        public object GetUsername(string Username) 
        {
            object res = parameter.GetUsernameBy(Username);
            return res;
         }



        /// <summary>
        /// 获取用户密码
        /// </summary>
        /// <param name="Username"></param>
        /// <returns></returns>
        public object GetUserPwd(int uid,string Username)
        {
            return parameter.GetUsernameByPwd(uid,Username);
       
        }
    }
}
